/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import database.DBConnection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.PropertiesManager;

/**
 *
 * @author Karen Aplica o padrão singleton, que deve ser criado conforme as
 * regras: - transformar o construtor em private - criar uma variavel do tipo da
 * propria classe como static - criar um método estático e publio que testa se
 * já existe uma instancia da classe
 */
public class UsuarioDAO implements OperacoesDAO {

    private Connection conexao;  
    
    public UsuarioDAO() throws ClassNotFoundException, SQLException {
        DBConnection db = new DBConnection();
        boolean teste = db.openConnection();
        if (teste == true) {
            conexao = db.getConexao();
        }
    }
    
    @Override
    public void inserir(Object obj) throws SQLException  {
        
        if (obj instanceof Usuario) {
            String SQL = PropertiesManager.getProperty("insert.usuario");            
            PreparedStatement st = conexao.prepareStatement(SQL);            
            Usuario user = (Usuario) obj;
            // INSERT INTO usr17.sl_usuario 
            // (id_usuario, nome, email, senha, cpf, rg, sexo) 
            // VALUES (s_sl_usuario.next_val,?,?,?,?,?,?)
            
            st.setString(1, user.getNome());
            st.setString(2, user.getEmail());
            st.setString(3, user.getSenha());
            st.setString(4, user.getCpf());
            st.setString(5, user.getRg());
            st.setString(6, user.getSexo());
            st.execute();
                       
            try {
                ContaDAO conta = new ContaDAO();
                conta.inserir(pesquisarPorEmail(user.getEmail()));
                
            } catch (ClassNotFoundException ex) {
                System.out.println(ex);
            }
        }
    }

    @Override
    public boolean excluir(Object obj) throws SQLException {
        throw new UnsupportedOperationException("Not supported ever.");
    }

    @Override
    public void editar(Object obj) throws SQLException {
        String SQL = PropertiesManager.getProperty("update.usuario");            
        PreparedStatement st = conexao.prepareStatement(SQL);            
        Usuario user = (Usuario) obj;

        // UPDATE usr17.sl_usuario SET nome=?, email=?, senha=?, sexo=? WHERE id_usuario=?

        st.setString(1, user.getNome());
        st.setString(2, user.getEmail());
        st.setString(3, user.getSenha());
        st.setString(4, user.getSexo());
        st.setInt(5, user.getId());

        st.execute();    
    }

    @Override
    public ArrayList pesquisarTudo() throws SQLException {
        ArrayList retorno = new ArrayList();
        String SQL = PropertiesManager.getProperty("selectAll.usuario");
        Statement st = conexao.createStatement();
        ResultSet res = st.executeQuery(SQL);
        
        ContaDAO contaDAO = new ContaDAO(conexao);
        while (res.next()) {            
            Usuario user = new Usuario(
                res.getInt("id_usuario"), 
                res.getString("rg"), 
                res.getString("cpf"), 
                res.getString("nome"), 
                0, 
                res.getString("sexo"), 
                res.getString("email"), 
                res.getString("senha"));
                    
            user.getContaUsuario().creditar(contaDAO.getSaldoByUsuario(user));
            
            retorno.add(user);
        }
        return retorno;
    }
    
    public Usuario pesquisarPorEmail(String email) throws SQLException, ClassNotFoundException{
        Usuario user = null;
        String SQL = PropertiesManager.getProperty("selectByEmail.usuario");
        PreparedStatement st = conexao.prepareStatement(SQL);
        st.setString(1, email);
        ResultSet res = st.executeQuery();
        
        while (res.next()) {
            user = new Usuario(
                res.getInt("id_usuario"), 
                res.getString("rg"), 
                res.getString("cpf"), 
                res.getString("nome"), 
                0, 
                res.getString("sexo"), 
                email, 
                res.getString("senha")
            );
        }
        
        if (user != null){
            ContaDAO contaDAO = new ContaDAO(conexao);
            user.getContaUsuario().creditar(contaDAO.getSaldoByUsuario(user));
        }
        
        return user;
    }
    
    public Usuario pesquisarPorId(int id) throws SQLException, ClassNotFoundException{
        Usuario user = null;
        String SQL = PropertiesManager.getProperty("selectById.usuario");
        PreparedStatement st = conexao.prepareStatement(SQL);
        st.setInt(1, id);
        ResultSet res = st.executeQuery();
        
        while (res.next()) {
            user = new Usuario(
                res.getInt("id_usuario"), 
                res.getString("rg"), 
                res.getString("cpf"), 
                res.getString("nome"), 
                0, 
                res.getString("sexo"), 
                res.getString("email"), 
                res.getString("senha")
            );
        }        
        if (user != null){
            ContaDAO contaDAO = new ContaDAO(conexao);
            user.getContaUsuario().creditar(contaDAO.getSaldoByUsuario(user));
        }
        
        return user;
    }
}
